﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SQLite;
using System.Web.UI.WebControls;

namespace DimensionalMeasurement
{
    class SQLiteHelper
    {

        public static string connectionString = "Data Source=D:/检测数据/数据库文件\\Test.db";

        /// <summary>
        /// 数据入库
        /// </summary>
        /// <param name="SQLString">SQLite语句</param>
        public static int ExecuteNonQuery(string SQLString)
        {
            SQLiteConnection conn = new SQLiteConnection(connectionString);
            int i = 0;
            try
            {
                conn.Open();
                if (conn.State.ToString() == "Open")
                {
                    SQLiteCommand cmd = new SQLiteCommand(SQLString, conn);
                    i = cmd.ExecuteNonQuery();

                }
            }
            catch (Exception ex)
            {
                conn.Close();
            }
            return i;
            conn.Close();
        }


        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataSet</returns>
        public static DataSet Query(string SQLString)
        {
            SQLiteConnection conn = new SQLiteConnection(connectionString);
            DataSet ds = new DataSet();
            PagedDataSource ps = new PagedDataSource();//实例化一个PagedDataSource对象，这是实现分页的核心
            try
            {
                conn.Open();
                SQLiteDataAdapter command = new SQLiteDataAdapter(SQLString, conn);
                command.Fill(ds, "ds");
            }
            catch (Exception ex)
            {
                conn.Close();
                throw new Exception(ex.Message);
            }
            return ds;
            conn.Close();
        }

        /// <summary>
        /// 读取所有用户名
        /// </summary>
        /// <returns></returns>
        public static List<User> GetUsers()
        {
            try
            {
                List<User> listUser = new List<User>();
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select * FROM tb_user");
                DataSet dsUser = Query(strSql.ToString());
                if (dsUser != null && dsUser.Tables.Count > 0 && dsUser.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow row in dsUser.Tables[0].Rows)
                    {
                        User user = new User()
                        {
                            Name = row["userName"].ToString(),
                            Password = row["password"].ToString(),
                            RealName = row["readName"].ToString(),
                            Rank = row["jb"].ToString()
                        };
                        listUser.Add(user);
                    }
                }
                return listUser;
            }
            catch (Exception ex)
            {
                return null;
            }
        }
        /// <summary>
        /// 是否存在
        /// </summary>
        /// <param name="userName">被查询的条件</param>
        /// <param name="passWord">被查询的条件</param>
        /// <returns></returns>
        public static bool Exists(string userName, string passWord)
        {



            string str = string.Format("select count(1) from tb_user where userName='{0}' AND  password='{1}' ", userName, passWord);
            try
            {
                SQLiteConnection conn = new SQLiteConnection(connectionString);
                conn.Open();
                SQLiteCommand cmd = new SQLiteCommand(str, conn);
                object obj = cmd.ExecuteScalar();
                int cmdresult;
                if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                {
                    cmdresult = 0;
                }
                else
                {
                    cmdresult = int.Parse(obj.ToString());
                }
                if (cmdresult == 0)
                {
                    return false;
                }
                else
                {
                    return true;
                }
                conn.Close();
            }
            catch (Exception ex)
            {
                return false;
            }
        }



        /// <summary>
        /// sqlite获取用户级别
        /// </summary>
        /// <param name="strUserName"></param>
        /// <returns></returns>
        public static int GetUserJbSqLite(string strUserName)
        {
            string strSql = string.Format(" SELECT jb FROM tb_user WHERE userName='{0}' ", strUserName);
            try
            {
                DataSet dsUserJB = Query(strSql);
                if (dsUserJB != null && dsUserJB.Tables.Count > 0 && dsUserJB.Tables[0].Rows.Count > 0)
                {
                    return int.Parse(dsUserJB.Tables[0].Rows[0]["jb"].ToString());
                }
            }
            catch
            {
                return 0;
            }
            return 0;
        }
    }
}
